/*
* BSM6.0
 *
 * Copyright 2012-2013 (C) SINODATA CO., LTD. All Rights Reserved.
 */
package com.sinodata.bsm.inmp.opconf;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.sinodata.bsm.common.vo.Res;
import com.sinodata.bsm.inmp.cache.CacheFactory;
import com.sinodata.bsm.inmp.cache.ResCache;
import com.sinodata.bsm.inmp.util.DBConnectionUtil;

/**
 * <p>
 * Description: 
 * </p>
 *
 * @author wangyazhou
 * @version 1.0

 * <p>
 * History: 
 *
 * Date                     Author         Version     Description
 * ---------------------------------------------------------------------------------
 * 2013-5-15 下午3:18:27          wangyazhou        1.0         To create
 * </p>
 *
 * @since 
 * @see     
 */
public class OpDbServiceUtil {

    private static final Logger logger = Logger.getLogger(OpDbServiceUtil.class);

    public static OpDbServiceUtil instance = new OpDbServiceUtil();

    private ResCache resCache = (ResCache) CacheFactory.getCache(ResCache.class);

    public static OpDbServiceUtil getInstance() {
        return instance;
    }

    private OpDbServiceUtil() {

    }

    /**
     * 根据sql获取单个指标值  如：当前时间   指标表名称    数据条数
     * @param sql
     * @return
     * @throws Exception
     */
    public String getSingleDbValue(String sql) throws Exception {

        logger.info(sql);

        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        String result = "";
        try {
            conn = DBConnectionUtil.getInstance().getManagerConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if (rs == null) {
                return null;
            }
            while (rs.next()) {
                result = rs.getString(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {

                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
            DBConnectionUtil.getInstance().returnConn(conn);
        }
        return result;
    }

    /**
     * 获取到所有的网络端口设备
     * @return String[] -- >inid、name、desc、ip、resTypeId -->index、netmask、speedin、speedout、mac、connip、sysoid、admin、oper
     */
    public List<String[]> getNetIFDetailList() {

        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        List<String[]> result = new ArrayList<String[]>();
        try {
            String sql = OpSqlGenerator.buildIfDetailSql();
            logger.info("if==" + sql);
            logger.info(sql);
            conn = DBConnectionUtil.getInstance().getManagerConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if (rs == null) {
                return null;
            }
            while (rs.next()) {
                String[] arys = new String[14];
                arys[0] = rs.getString(1);
                arys[1] = rs.getString(2);
                arys[2] = rs.getString(3);
                arys[3] = rs.getString(4);
                arys[4] = rs.getString(5);
                arys[5] = rs.getString(6);
                arys[6] = rs.getString(7);
                arys[7] = rs.getString(8);
                arys[8] = rs.getString(9);
                arys[9] = rs.getString(10);
                arys[10] = rs.getString(11);
                arys[11] = rs.getString(12);
                arys[12] = rs.getString(13);
                arys[13] = rs.getString(14);

                result.add(arys);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
            DBConnectionUtil.getInstance().returnConn(conn);
        }
        return result;
    }

    /**
     * 获取到所有的网络设备详细列表
     * @return String[] -- >inid、name、desc、ip、resTypeId -->sysoid、verdor、desc
     */
    public List<String[]> getNetDeviceDetailList() {

        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        List<String[]> result = new ArrayList<String[]>();
        try {
            String sql = OpSqlGenerator.buildNwDetailSql();
            logger.info(sql);
            conn = DBConnectionUtil.getInstance().getManagerConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if (rs == null) {
                return null;
            }
            while (rs.next()) {
                String[] arys = new String[8];
                arys[0] = rs.getString(1);
                arys[1] = rs.getString(2);
                arys[2] = rs.getString(3);
                arys[3] = rs.getString(4);
                arys[4] = rs.getString(5);
                arys[5] = rs.getString(6);
                arys[6] = rs.getString(7);
                arys[7] = rs.getString(8);

                result.add(arys);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
            DBConnectionUtil.getInstance().returnConn(conn);
        }
        return result;
    }

    /**
     * 获取到所有的网络设备
     * @return String[] -- >inid、name、desc、ip、resTypeId
     */
    public List<String[]> getNetDeviceList() {

        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        List<String[]> result = new ArrayList<String[]>();
        try {
            String sql = OpSqlGenerator.buildNwListSql();
            logger.info(sql);
            conn = DBConnectionUtil.getInstance().getManagerConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if (rs == null) {
                return null;
            }
            while (rs.next()) {
                String[] arys = new String[5];
                arys[0] = rs.getString(1);
                arys[1] = rs.getString(2);
                arys[2] = rs.getString(3);
                arys[3] = rs.getString(4);
                arys[4] = rs.getString(5);

                result.add(arys);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
            DBConnectionUtil.getInstance().returnConn(conn);
        }
        return result;
    }

    /**
     * 
     * @param kpiName
     * @return inid、name、desc、resTypeId -->index、netmask、speedin、speedout、mac、connip、sysoid、admin、oper ,name
     */
    public List<String[]> getIFDetailList(String inid) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        List<String[]> result = new ArrayList<String[]>();
        try {
            String sql = OpSqlGenerator.bulidIfByNWSql(inid);
            Res res = resCache.getResIdByIId(inid);
            if (res == null) {
                return null;
            }
            String portType = OpConfManager.getInstance().getNwPortMap().get(res.getResTypeId());

            logger.info(sql);
            conn = DBConnectionUtil.getInstance().getManagerConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if (rs == null) {
                return null;
            }
            while (rs.next()) {
                String[] arys = new String[14];
                arys[0] = rs.getString(1);
                arys[1] = rs.getString(2);
                arys[2] = rs.getString(3);
                arys[3] = portType;
                arys[4] = rs.getString(5);
                arys[5] = rs.getString(6);
                arys[6] = rs.getString(7);
                arys[7] = rs.getString(8);
                arys[8] = rs.getString(9);
                arys[9] = rs.getString(10);
                arys[10] = rs.getString(11);
                arys[11] = rs.getString(12);
                arys[12] = rs.getString(13);
                arys[13] = rs.getString(14);
                result.add(arys);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException ex) {
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
            DBConnectionUtil.getInstance().returnConn(conn);
        }
        return result;
    }

}
